package com.idlecode.config.query;


import cn.hutool.core.util.StrUtil;
import com.idlecode.config.DbType;

/**
 * Oracle查询
 */
public class OracleQuery implements AbstractQuery {

    @Override
    public DbType dbType() {
        return DbType.Oracle;
    }

    @Override
    public String dbNamesSql() {
        return "SELECT DISTINCT username AS OWNER FROM dba_users ORDER BY OWNER";
    }

    @Override
    public String tableSql(String dataBaseName, String tableName) {
        StringBuilder sql = new StringBuilder();
        sql.append("select dt.table_name, dtc.comments from");
        sql.append(" all_tables dt left join all_tab_comments dtc on dt.table_name = dtc.table_name and dt.owner = dtc.owner");
        sql.append(" where 1 = 1 ");
        //数据库查询
        if (StrUtil.isNotBlank(dataBaseName)) {
            sql.append("and dt.owner = '").append(dataBaseName).append("' ");
        }
        // 表名查询
        if (StrUtil.isNotBlank(tableName)) {
            sql.append("and dt.table_name = '").append(tableName).append("' ");
        }
        sql.append("order by dt.table_name asc");

        return sql.toString();
    }

    @Override
    public String dbName() {
        return "OWNER";
    }

    @Override
    public String tableName() {
        return "table_name";
    }

    @Override
    public String tableComment() {
        return "comments";
    }

    @Override
    public String tableUpdateTime() {
        return "update_time";
    }

    @Override
    public String tableFieldsSql(String dataBaseName, String tableName) {
        return StrUtil.format("SELECT\n" +
                "  A.COLUMN_NAME,\n" +
                "  A.DATA_TYPE,\n" +
                "  B.COMMENTS,\n" +
                "  A.DATA_PRECISION,\n" +
                "  A.DATA_SCALE,\n" +
                "  DECODE(C.POSITION, '1', 'PRI') KEY\n" +
                "FROM\n" +
                "  ALL_TAB_COLUMNS A\n" +
                "  INNER JOIN ALL_COL_COMMENTS B ON A.TABLE_NAME = B.TABLE_NAME\n" +
                "  AND A.COLUMN_NAME = B.COLUMN_NAME\n" +
                "  AND B.OWNER = '{}'\n" +
                "  LEFT JOIN ALL_CONSTRAINTS D ON D.TABLE_NAME = A.TABLE_NAME\n" +
                "  AND D.CONSTRAINT_TYPE = 'P'\n" +
                "  AND D.OWNER = '{}'\n" +
                "  LEFT JOIN ALL_CONS_COLUMNS C ON C.CONSTRAINT_NAME = D.CONSTRAINT_NAME\n" +
                "  AND C.COLUMN_NAME = A.COLUMN_NAME\n" +
                "  AND C.OWNER = '{}'\n" +
                "WHERE\n" +
                "  A.OWNER = '{}'\n" +
                "  AND A.TABLE_NAME = '{}'\n" +
                "ORDER BY\n" +
                "  A.COLUMN_ID", dataBaseName, dataBaseName, dataBaseName, dataBaseName, tableName);
    }

    @Override
    public String fieldName() {
        return "COLUMN_NAME";
    }


    @Override
    public String fieldType() {
        return "DATA_TYPE";
    }


    @Override
    public String fieldComment() {
        return "COMMENTS";
    }


    @Override
    public String fieldKey() {
        return "KEY";
    }

    @Override
    public String dataPrecision() {
        return "DATA_PRECISION";
    }

    @Override
    public String dataScale() {
        return "DATA_SCALE";
    }
}
